package myservice.android.database;

import java.io.File;
import java.util.Random;

import myservice.android.Global;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;
import android.util.Log;

public class Database {
	
	public static DatabaseHelper databaseHelper;
	public static SQLiteDatabase database;
	public static Context context;
	
	public static void openConnection() {
		Log.i(Global.TAG,"Opening the database...");
		Database.databaseHelper = new DatabaseHelper(context);
		Database.database = Database.databaseHelper.getWritableDatabase();	
		
	}

	/** Closes the database */
	public static void closeConnection()	{
		Log.i(Global.TAG,"Closing the database...");
		databaseHelper.close();
	}
	
	/** Executes an SQL statement that does not require to return anything */
	public static void execSQL(String sql) {
		Log.d(Global.TAG,sql);
		database.execSQL(sql);
	}
	
	/** Executes a SELECT statement in the database */
	public static Cursor select(String sql) {
		Log.d(Global.TAG,sql);
		return database.rawQuery(sql, null);
	}
	
	/** Deletes MyService database */
	public static void deleteDatabase(Context context) {
		Log.i(Global.TAG,"Deleting the database...");
		if (context.deleteDatabase(Global.databaseName)!=true)
			Log.i(Global.TAG,"Failure!");
		else
			Log.i(Global.TAG,"Success!");
	}
	
	public static void deleteInterestedPerson(long personId) {
		Database.execSQL("UPDATE persons SET timestamp="+Global.TIMESTAMP_DELETED+" WHERE personid="+personId);
		Database.execSQL("UPDATE calls SET timestamp="+Global.TIMESTAMP_DELETED+" WHERE personid="+personId+" AND territoryid=0 AND timestamp<>"+Global.TIMESTAMP_DELETED);
		Database.execSQL("UPDATE calls SET personid=0, timestamp="+Global.getTimeStamp()+" WHERE personid="+personId+" AND timestamp<>"+Global.TIMESTAMP_DELETED);
	}	
	
	public static void deleteCall(long callId) {
		Database.execSQL("UPDATE calls SET timestamp="+Global.TIMESTAMP_DELETED+" WHERE callid="+callId);		
	}
	
	public static void deletePublication(long publicationId) {
		Database.execSQL("UPDATE publications SET timestamp="+Global.TIMESTAMP_DELETED+" WHERE publicationid="+publicationId);
		Database.execSQL("UPDATE placedpublications SET timestamp="+Global.TIMESTAMP_DELETED+" WHERE publicationid="+publicationId);		
	}
	
	public static void deleteTerritoryElement(long territoryId) {
							
		Database.execSQL("UPDATE territories SET timestamp="+Global.TIMESTAMP_DELETED+" WHERE territoryid="+territoryId);
		Database.execSQL("UPDATE calls SET territoryid=0, timestamp="+Global.getTimeStamp()+" WHERE territoryid="+territoryId+" AND timestamp<>"+Global.TIMESTAMP_DELETED);
		
    	Cursor cursor = Database.select("SELECT territoryid FROM territories WHERE parent="+territoryId);
    	while (cursor.moveToNext()) {	
    		deleteTerritoryElement(cursor.getInt(cursor.getColumnIndex("territoryid")));
    	}	
    	cursor.close(); 
	
	}
	
	// Returns a random row ID that is not already present in the provided table
	public static int getUniqueRowId(String table) {
		
		int id = 0;	
		Random randomGenerator = new Random();
		int count = 0;		
		
		do {		
			id = randomGenerator.nextInt(2000000000)+1;				
			Cursor c;
			if (table.equals("persons"))				
				c = select("SELECT COUNT(*) AS c FROM persons WHERE personid="+id);
			else if (table.equals("calls"))
				c = select("SELECT COUNT(*) AS c FROM calls WHERE callid="+id);
			else if (table.equals("territories"))
				c = select("SELECT COUNT(*) AS c FROM territories WHERE territoryid="+id);				
			else
				c = select("SELECT COUNT(*) AS c FROM publications WHERE publicationid="+id);			
			c.moveToNext();
			count = c.getInt(c.getColumnIndex("c"));
			c.close();
		} while (count>0);
		
		return id;
		
	}	
	
	public static boolean backUpDatabase() {		
		Log.i(Global.TAG,"Backing up the database...");
		Database.closeConnection();						
		String sourcePath = new File(Environment.getDataDirectory(), "//data//myservice.android//databases//"+Global.databaseName).getPath();
		String destinationPath = new File(Environment.getDataDirectory(), "//data//myservice.android//databases//backup.db").getPath();
		boolean result = Global.copyFile(sourcePath,destinationPath);
		Database.openConnection();				
		return result;
	}
	
	public static boolean restoreDatabaseFromBackup() {		
		Log.i(Global.TAG,"Restoring database from the backup...");
		Database.closeConnection();						
		String sourcePath = new File(Environment.getDataDirectory(), "//data//myservice.android//databases//backup.db").getPath();
		String destinationPath = new File(Environment.getDataDirectory(), "//data//myservice.android//databases//"+Global.databaseName).getPath();
		boolean result = Global.copyFile(sourcePath,destinationPath);
		Database.openConnection();				
		return result;
	}
	
	public static boolean checkDatabaseBackup() {
		
		Log.i(Global.TAG,"Checking last autobackup date...");
		
		long monthInMilliseconds = 604800000L;		                           
		
		long currentTimestamp = Global.getTimeStamp();
		
		// Get the date of last autobackup
		Cursor c = Database.select("SELECT value FROM settings WHERE name='lastbackup'");
		c.moveToNext();
		long lastBackupTimestamp = Long.parseLong(c.getString(c.getColumnIndex("value")));
		c.close();		
		
		if (currentTimestamp-lastBackupTimestamp>monthInMilliseconds) { // time since last backup is more than 30 days
			Log.i(Global.TAG,"Automatic backup the database...");
			Database.execSQL("UPDATE settings SET value='"+currentTimestamp+"' WHERE name='lastbackup'");
			Database.closeConnection();						
			String sourcePath = new File(Environment.getDataDirectory(), "//data//myservice.android//databases//"+Global.databaseName).getPath();
			String destinationPath = new File(Environment.getExternalStorageDirectory().getPath()+"/MyService/MyService_android_backup.db").getPath();
			Global.copyFile(sourcePath,destinationPath);
			Database.openConnection();
			
			return true;
		}
		
		return false;
		
	}
	
}
